package com.bz.xtcx.manager.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bz.xtcx.manager.entity.*;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {

    /** 日志. */
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);


    
    public static void main(String[] args) {
        String s = "德勤中国高科技高成长50强\\2017-01-01\\";
        String[] ss = s.split("\\\\", -1);
        System.out.println(ss.length);
        
//        String strJson = "{ \"学历\":[{\"1\":\"小学\"},{\"2\":\"初中\"},{\"3\":\"高中\"},{\"4\":\"大专\"},{\"5\":\"本科\"},{\"6\":\"研究生\"},{\"7\":\"博士\"}],"
//                + "\"学位\":[{\"1\":\"学士\"},{\"2\":\"硕士\"},{\"3\":\"博士\"}],"
//                + "\"研究领域\":[{\"1\":\"智能装备\"},{\"2\":\"电子信息\"},{\"3\":\"新材料\"},{\"4\":\"航空航天\"},{\"5\":\"生物技术与新医药\"},{\"6\":\"能源与环保\"},{\"7\":\"管理\"}],"
//                + "\"职称级别\":[{\"1\":\"正高\"},{\"2\":\"副高\"}]}";
//        ExcelUtil eu = new ExcelUtil();
//        JSONArray ja = eu.excelTranJson("e:/expert.xls", 1, strJson);
//        System.out.println(ja.size());
//        JSONObject json = JSON.parseObject(strJson);
//        JSONArray jsons = json.getJSONArray("学历");
//        System.out.println(jsons);
//
//        String value = "[\"1\",\"7\"]";
//        JSONArray val = JSON.parseArray(value);
//        System.out.println(val);
//        for(Object s : val) {
//            System.out.println(s);
//            for(int i = 0;i<jsons.size();i++) {
//                if(jsons.getJSONObject(i).get(s) != null) {
//                    System.out.println(jsons.getJSONObject(i).get(s));
//                }
//
//            }
//        }
    }

    static HSSFCellStyle getStyle(HSSFWorkbook wb) {
        Font font = wb.createFont();
        font.setBold(true);
        HSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(CellStyle.ALIGN_CENTER);
        //style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //style.setFillForegroundColor(HSSFColor.YELLOW.index);
        style.setAlignment(HorizontalAlignment.CENTER); // 居中  
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFont(font);
        style.setBorderLeft(BorderStyle.DASH_DOT_DOT);//左边框
        style.setWrapText(true); //设置换行
        return style;
    }

    static XSSFCellStyle getXSSFStyle(XSSFWorkbook wb) {
        Font font = wb.createFont();
        font.setBold(true);
        XSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(CellStyle.ALIGN_CENTER);
        //style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //style.setFillForegroundColor(HSSFColor.YELLOW.index);
        style.setAlignment(HorizontalAlignment.CENTER); // 居中
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFont(font);
        style.setBorderLeft(BorderStyle.DASH_DOT_DOT);//左边框
        style.setWrapText(true); //设置换行
        return style;
    }

    public static void writeBusUserExcel(List<BusUser> list, OutputStream out) {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("注册账户列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;

        XSSFCellStyle style = getXSSFStyle(wb);

        int count = 0;
        String[] columns = {"序号", "账号", "用户名", "手机号", "用户类别", "名称", "证件号", "是否同意平台服务协议", "激活状态", "用户状态"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 2048);
            count++;
        }

        count = 1;
        for (BusUser lib : list) {
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getEmail()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getUserName()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getCellphone()));
            c = row.createCell(4);
            int ut = lib.getUserType();
            String utStr = "";
            if (1 == ut) {
                utStr = "个人/专家";
            } else if (2 == ut) {
                utStr = "企业";
            } else if (3 == ut) {
                utStr = "服务机构";
            } else if (4 == ut) {
                utStr = "高校院所";
            } else {
                utStr = "未知类型";
            }
            c.setCellValue(utStr);
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(7);
            int ag = lib.getAgreement();
            String agStr = "";
            if (0 == ag) {
                agStr = "不同意";
            } else if (1 == ag) {
                agStr = "同意";
            } else {
                agStr = "未知";
            }
            c.setCellValue(agStr);
            c = row.createCell(8);
            int cs = lib.getCheckStatus();
            String csStr = "";
            if (0 == cs) {
                csStr = "未激活";
            } else if (1 == cs) {
                csStr = "已激活";
            } else if (2 == cs) {
                csStr = "已加入平台";
            } else {
                csStr = "未知";
            }
            c.setCellValue(csStr);
            c = row.createCell(9);
            int st = lib.getStatus();
            String stStr = "";
            if (0 == st) {
                stStr = "禁用";
            } else if (1 == st) {
                stStr = "正常";
            } else {
                stStr = "未知";
            }
            c.setCellValue(stStr);
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出注册用户列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeExEnrollExcel(List<CmsExchangesEnroll> list, OutputStream out) {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("活动报名列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;

        XSSFCellStyle style = getXSSFStyle(wb);

        int count = 0;
        String[] columns = {"序号", "姓名", "工作单位", "职务", "联系电话", "联系邮箱", "联系地址", "报名时间", "备注"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 2048);
            count++;
        }

        count = 1;
        for (CmsExchangesEnroll lib : list) {
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getWorkplace()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getJob()));
            c = row.createCell(4);
            c.setCellValue(WzStrUtil.defaultString(lib.getPhone()));
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getEmail()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddr()));
            c = row.createCell(7);
            c.setCellValue(WzStrUtil.defaultString(WzDateUtil.dt2Str(lib.getEnrollDate(), WzDateUtil.DT_FMT_5)));
            c = row.createCell(8);
            c.setCellValue(WzStrUtil.defaultString(lib.getMemo()));
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出活动报名列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeExpertExcel(List<LibExpert> list, String strJson, OutputStream out) {
        JSONObject jsonStr = JSON.parseObject(strJson);

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("专家列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        HSSFCellStyle style = getStyle(wb);
        Row row = sheet.createRow(0);
        Cell c = null;

        int count = 0;
        String[] columns = {"序号", "编号", "姓名", "身份证号", "性别", "出生年月", "所在区域", "通讯地址", "毕业院校", "学历", "学位", "研究领域", "研究领域（其他）", "研究方向",
                "工作单位", "工作部门", "现任职务", "职称", "职称级别", "手机号码", "办公电话", "邮箱", "主要学术成就/研究成果/管理成就", "主要产学研合作项目情况（国防军工类项目）", "近5年专业研究及获奖情况"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }
        count = 1;
        int index = 0;
        for (LibExpert lib : list) {
            row = sheet.createRow(count++);
            index = 0;
            c = row.createCell(index++);
            c.setCellValue(count - 1);
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getNumber()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getSex() == null ? "-" : lib.getSex().equals("0") ? "女" : "男"));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getBirthday()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.getPcaa(lib.getCountry())));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddress()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getSchool()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "学历", lib.getEducation())));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "学位", lib.getDegree())));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "研究领域", lib.getResearchField())));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getResearchFieldOther()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getResearchArea()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getWorkUnit()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getWorkDepartment()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getJobTitle()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getTitle()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "职称级别", lib.getTitleLevel())));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getCellphone()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getTelephone()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getEmail()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getSuccessRecord()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(lib.getProjectDesc()));
            c = row.createCell(index++);
            // 处理解析近5年专业研究及获奖情况列表
            String rwStr = lib.getResearchWinning();
            if (WzStrUtil.isNotBlank(lib.getResearchWinning())) {
                try {
                    JSONArray rwJsonArr = JSON.parseArray(lib.getResearchWinning());
                    JSONObject rwJsonRow = null;
                    StringBuilder tmpRowStr = new StringBuilder("");
                    for (int i = 0; i < rwJsonArr.size(); i++) {
                        rwJsonRow = rwJsonArr.getJSONObject(i);
                        tmpRowStr.append("项目课题名:" + WzStrUtil.defaultString(rwJsonRow.getString("projectname"))).append("|");
                        tmpRowStr.append("项目课题来源:" + WzStrUtil.defaultString(rwJsonRow.getString("projectSrc"))).append("|");
                        tmpRowStr.append("完成情况:" + WzStrUtil.defaultString(rwJsonRow.getString("finishcon"))).append("|");
                        tmpRowStr.append("完成时间:" + WzStrUtil.defaultString(rwJsonRow.getString("finishtime"))).append("|");
                        tmpRowStr.append("奖项名称:" + WzStrUtil.defaultString(rwJsonRow.getString("rewname"))).append("|");
                        tmpRowStr.append("获奖等级:" + WzStrUtil.defaultString(rwJsonRow.getString("rewlevel"))).append("|");
                        tmpRowStr.append("获奖时间:" + WzStrUtil.defaultString(rwJsonRow.getString("rewtime"))).append("|");
                        tmpRowStr.append("\r\n");
                    }
                    rwStr = tmpRowStr.toString();
                } catch (Exception e) {
                    logger.error("解析近5年专业研究及获奖情况异常", e);
                }
            }
            c.setCellValue(rwStr);
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出专家列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeCollegeExcel(List<LibCollege> list, String strJson, OutputStream out) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("高校院所列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;
        HSSFCellStyle style = getStyle(wb);

        int count = 0;
        String[] columns = {"序号", "编号", "名称", "统一社会信用代码", "所在地区", "通信地址", "邮编", "单位网址", "单位简介", "重大平台", "联系人姓名", "联系人手机", "联系人邮箱"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }
        count = 1;
        for (LibCollege lib : list) {
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getNumber()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(4);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.getPcaa(lib.getCountry())));
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddress()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(lib.getZipCode()));
            c = row.createCell(7);
            c.setCellValue(WzStrUtil.defaultString(lib.getUnitUrl()));
            c = row.createCell(8);
            c.setCellValue(WzStrUtil.defaultString(lib.getIntroduction()));
            c = row.createCell(9);
            c.setCellValue(WzStrUtil.defaultString(lib.getMajorPlatform()));
            c = row.createCell(10);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxName()));
            c = row.createCell(11);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxMobile()));
            c = row.createCell(12);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxEmail()));
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出高校院所列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeReqExcel(List<LibRequirements> list, String strJson, OutputStream out) {
        JSONObject jsonStr = JSON.parseObject(strJson);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("需求列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;

        HSSFCellStyle style = getStyle(wb);

        int count = 0;
        String[] columns = {"序号", "编号", "技术需求名称", "需求单位名称", "统一社会信用代码", "通信地址", "所在地区", "负责人", "职务", "电话", "邮箱", "联系人", "职务", "电话", "邮箱",
                "所属领域", "所属领域（其他）", "希望解决的技术需求概述", "要求达到的技术性能、参数指标等", "其他相关要求", "需求背景", "需求背景（其他）", "意向获得方式"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }

        count = 1;
        for (LibRequirements lib : list) {
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getNumber()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getReqName()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(4);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddress()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.getPcaa(lib.getCountry())));
            c = row.createCell(7);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzname()));
            c = row.createCell(8);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzzw()));
            c = row.createCell(9);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzphone()));
            c = row.createCell(10);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzemail()));
            c = row.createCell(11);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzname2()));
            c = row.createCell(12);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzzw2()));
            c = row.createCell(13);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzphone2()));
            c = row.createCell(14);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzemail2()));
            c = row.createCell(15);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所属领域", lib.getDomain())));
            c = row.createCell(16);
            c.setCellValue(WzStrUtil.defaultString(lib.getDomainOther()));
            c = row.createCell(17);
            c.setCellValue(WzStrUtil.defaultString(lib.getReqDesc()));
            c = row.createCell(18);
            c.setCellValue(WzStrUtil.defaultString(lib.getReqNew()));
            c = row.createCell(19);
            c.setCellValue(WzStrUtil.defaultString(lib.getReqOther()));
            c = row.createCell(20);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "需求背景", lib.getReqBack())));
            c = row.createCell(21);
            c.setCellValue(WzStrUtil.defaultString(lib.getReqBackOther()));
            c = row.createCell(22);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "合作方式", lib.getReqGet())));
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出需求列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeResExcel(List<LibResults> list, String strJson, OutputStream out) {
        JSONObject jsonStr = JSON.parseObject(strJson);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("成果列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;

        HSSFCellStyle style = getStyle(wb);

        int count = 0;
        String[] columns = {"序号", "编号", "技术成果名称", "企业名称", "统一社会信用代码", "通信地址", "所在地区", "项目负责人", "电话", "邮箱", "项目联系人", "电话", "邮箱",
                "所属领域", "所属领域（其他）", "技术成果简介", "可应用领域", "技术创新点", "已申请专利（件）", "获授权专利（件）", "其他知识产权（件）", "知识产权情况", "所处阶段", "合作方式"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }

        count = 1;
        for (LibResults lib : list) {
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getNumber()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getResName()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(4);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddress()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.getPcaa(lib.getCountry())));
            c = row.createCell(7);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzname()));
            c = row.createCell(8);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzphone()));
            c = row.createCell(9);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzemail()));
            c = row.createCell(10);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzname2()));
            c = row.createCell(11);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzphone2()));
            c = row.createCell(12);
            c.setCellValue(WzStrUtil.defaultString(lib.getFzemail2()));
            c = row.createCell(13);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所属领域", lib.getDomain())));
            c = row.createCell(14);
            c.setCellValue(WzStrUtil.defaultString(lib.getDomainOther()));
            c = row.createCell(15);
            c.setCellValue(WzStrUtil.defaultString(lib.getResDesc()));
            c = row.createCell(16);
            c.setCellValue(WzStrUtil.defaultString(lib.getUseArea()));
            c = row.createCell(17);
            c.setCellValue(WzStrUtil.defaultString(lib.getNewIdea()));
            c = row.createCell(18);
            c.setCellValue(WzStrUtil.defaultString(lib.getSqPatent()));
            c = row.createCell(19);
            c.setCellValue(WzStrUtil.defaultString(lib.getHavePatent()));
            c = row.createCell(20);
            c.setCellValue(WzStrUtil.defaultString(lib.getOtherPatent()));
            // 知识产权情况
            c = row.createCell(21);
            String patStr = lib.getPatents();
            if (WzStrUtil.isNotBlank(lib.getPatents())) {
                try {
                    JSONArray patJsonArr = JSON.parseArray(lib.getPatents());
                    JSONObject patJsonRow = null;
                    StringBuilder tmpRowStr = new StringBuilder("");
                    for (int i = 0; i < patJsonArr.size(); i++) {
                        patJsonRow = patJsonArr.getJSONObject(i);
                        tmpRowStr.append("序号:" + (i + 1)).append("；");
                        tmpRowStr.append("专利名:" + WzStrUtil.defaultString(patJsonRow.getString("projectname"))).append("|");
                        tmpRowStr.append("专利类型:" + WzStrUtil.defaultString(patJsonRow.getString("projecType"))).append("|");
                        tmpRowStr.append("专利号:" + WzStrUtil.defaultString(patJsonRow.getString("projectNum"))).append("|");
                        tmpRowStr.append("专利人:" + WzStrUtil.defaultString(patJsonRow.getString("projectPer"))).append("|");
                        tmpRowStr.append("有效期:" + WzStrUtil.defaultString(patJsonRow.getString("projectTime"))).append("|");
                        tmpRowStr.append("\r\n");
                    }
                    patStr = tmpRowStr.toString();
                } catch (Exception e) {
                    logger.error("解析知识产权情况异常", e);
                }
            }
            c.setCellValue(patStr);
            c = row.createCell(22);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所处阶段", lib.getStage())));
            c = row.createCell(23);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "合作方式", lib.getCooperation())));
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出成果列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeServiceExcel(List<LibServices> list, String strJson, OutputStream out) {
        JSONObject jsonStr = JSON.parseObject(strJson);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("服务机构列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;

        HSSFCellStyle style = getStyle(wb);

        int count = 0;
        String[] columns = {"序号", "编号", "单位名称", "统一社会信用代码", "所在地区", "通信地址", "邮编", "机构性质", "机构性质（其他）", "机构类别", "机构类别（其他）", "机构法人姓名", "机构法人电话", "机构法人手机", "机构法人邮箱", "机构联系人姓名", "机构联系人职务", "机构联系人电话", "机构联系人手机", "机构联系人邮箱",
                "去年服务收入（万元）", "去年服务数量（次）", "前年服务收入（万元）", "前年服务数量（次）", "大前年服务收入（万元）", "大前年服务数量（次）", "人员总数", "本科及以上学历人员数", "中高级职称以上人员数", "承担的政府项目情况", "所获资质及荣誉", "主要工作成效", "可提供的服务简介"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }

        count = 1;
        for (LibServices lib : list) {
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getNumber()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(4);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.getPcaa(lib.getCountry())));
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddress()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(lib.getZipCode()));
            c = row.createCell(7);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "机构性质", lib.getOrgNature())));
            c = row.createCell(8);
            c.setCellValue(WzStrUtil.defaultString(lib.getOrgNatureOther()));
            c = row.createCell(9);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "机构类别", lib.getOrgType())));
            c = row.createCell(10);
            c.setCellValue(WzStrUtil.defaultString(lib.getOrgTypeOther()));
            c = row.createCell(11);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpname()));
            c = row.createCell(12);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpphone()));
            c = row.createCell(13);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpmobile()));
            c = row.createCell(14);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpemail()));
            c = row.createCell(15);
            c.setCellValue(WzStrUtil.defaultString(lib.getLinkman()));
            c = row.createCell(16);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxzw()));
            c = row.createCell(17);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxphone()));
            c = row.createCell(18);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxmobile()));
            c = row.createCell(19);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxemail()));
            c = row.createCell(20);
            c.setCellValue(WzStrUtil.defaultString(lib.getServiceAmountLast()));
            c = row.createCell(21);
            c.setCellValue(WzStrUtil.defaultString(lib.getServiceQuantityLast()));
            c = row.createCell(22);
            c.setCellValue(WzStrUtil.defaultString(lib.getServiceAmountBefore()));
            c = row.createCell(23);
            c.setCellValue(WzStrUtil.defaultString(lib.getServiceQuantityBefore()));
            c = row.createCell(24);
            c.setCellValue(WzStrUtil.defaultString(lib.getServiceAmountPrevious()));
            c = row.createCell(25);
            c.setCellValue(WzStrUtil.defaultString(lib.getServiceQuantityPrevious()));
            c = row.createCell(26);
            c.setCellValue(WzStrUtil.defaultString(lib.getPersonnelNumber()));
            c = row.createCell(27);
            c.setCellValue(WzStrUtil.defaultString(lib.getUndergraduateNumber()));
            c = row.createCell(28);
            c.setCellValue(WzStrUtil.defaultString(lib.getMiddleTitleNumber()));

            // 承担政府项目
            c = row.createCell(29);
            String pjStr = lib.getProject();
            if (WzStrUtil.isNotBlank(lib.getProject())) {
                try {
                    JSONArray pjJsonArr = JSON.parseArray(lib.getProject());
                    JSONObject pjJsonRow = null;
                    StringBuilder tmpRowStr = new StringBuilder("");
                    for (int i = 0; i < pjJsonArr.size(); i++) {
                        pjJsonRow = pjJsonArr.getJSONObject(i);
                        tmpRowStr.append("项目名:" + WzStrUtil.defaultString(pjJsonRow.getString("name"))).append("|");
                        JSONArray tmpDateArr = pjJsonRow.getJSONArray("time");
                        if (null == tmpDateArr || 0 >= tmpDateArr.size()) {
                            tmpRowStr.append("时间起:未填写|");
                            tmpRowStr.append("时间止:未填写|");
                        } else if (1 == tmpDateArr.size()) {
                            tmpRowStr.append("时间起:" + WzStrUtil.defaultString((String) tmpDateArr.get(0))).append("|");
                            tmpRowStr.append("时间止:未填写|");
                        } else {
                            tmpRowStr.append("时间起:" + WzStrUtil.defaultString((String) tmpDateArr.get(0))).append("|");
                            tmpRowStr.append("时间止:" + WzStrUtil.defaultString((String) tmpDateArr.get(1))).append("|");
                        }
                        tmpRowStr.append("项目来源:" + WzStrUtil.defaultString(pjJsonRow.getString("source"))).append("|");
                        tmpRowStr.append("资助方式:" + WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "资助方式", pjJsonRow.getString("registerSupport")))).append("|");
                        tmpRowStr.append("资助方式（其他）:" + WzStrUtil.defaultString(pjJsonRow.getString("mechregisterSupportOther"))).append("|");
                        tmpRowStr.append("执行情况:" + WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "执行情况", pjJsonRow.getString("registerImplement")))).append("|");
                        tmpRowStr.append("\r\n");
                    }
                    pjStr = tmpRowStr.toString();
                } catch (Exception e) {
                    logger.error("解析承担政府项目情况异常", e);
                }
            }
            c.setCellValue(pjStr);
            // 所获资质和荣誉
            c = row.createCell(30);
            String hoStr = lib.getHonor();
            if (WzStrUtil.isNotBlank(lib.getHonor())) {
                try {
                    JSONArray hoJsonArr = JSON.parseArray(lib.getHonor());
                    JSONObject hoJsonRow = null;
                    StringBuilder tmpRowStr = new StringBuilder("");
                    for (int i = 0; i < hoJsonArr.size(); i++) {
                        hoJsonRow = hoJsonArr.getJSONObject(i);
                        tmpRowStr.append("荣誉名:" + WzStrUtil.defaultString(hoJsonRow.getString("name"))).append("|");
                        tmpRowStr.append("获得时间:" + WzStrUtil.defaultString(hoJsonRow.getString("time"))).append("|");
                        tmpRowStr.append("颁发机构:" + WzStrUtil.defaultString(hoJsonRow.getString("issuingAgency"))).append("|");
                        tmpRowStr.append("\r\n");
                    }
                    hoStr = tmpRowStr.toString();
                } catch (Exception e) {
                    logger.error("解析所获资质和荣誉情况异常", e);
                }
            }
            c.setCellValue(hoStr);
            // 主要工作成效
            c = row.createCell(31);
            String wrStr = lib.getWorkResult();
            if (WzStrUtil.isNotBlank(lib.getWorkResult())) {
                try {
                    JSONArray wrJsonArr = JSON.parseArray(lib.getWorkResult());
                    JSONObject wrJsonRow = null;
                    StringBuilder tmpRowStr = new StringBuilder("");
                    for (int i = 0; i < wrJsonArr.size(); i++) {
                        wrJsonRow = wrJsonArr.getJSONObject(i);
                        tmpRowStr.append("服务项目名:" + WzStrUtil.defaultString(wrJsonRow.getString("name"))).append("|");
                        tmpRowStr.append("服务对象:" + WzStrUtil.defaultString(wrJsonRow.getString("object"))).append("|");
                        JSONArray tmpDateArr = wrJsonRow.getJSONArray("time");
                        if (null == tmpDateArr || 0 >= tmpDateArr.size()) {
                            tmpRowStr.append("服务时间起:未填写|");
                            tmpRowStr.append("服务时间止:未填写|");
                        } else if (1 == tmpDateArr.size()) {
                            tmpRowStr.append("服务时间起:" + WzStrUtil.defaultString((String) tmpDateArr.get(0))).append("|");
                            tmpRowStr.append("服务时间止:未填写|");
                        } else {
                            tmpRowStr.append("服务时间起:" + WzStrUtil.defaultString((String) tmpDateArr.get(0))).append("|");
                            tmpRowStr.append("服务时间止:" + WzStrUtil.defaultString((String) tmpDateArr.get(1))).append("|");
                        }
                        tmpRowStr.append("\r\n");
                    }
                    wrStr = tmpRowStr.toString();
                } catch (Exception e) {
                    logger.error("解析主要工作成效情况异常", e);
                }
            }
            c.setCellValue(wrStr);
            c = row.createCell(32);
            c.setCellValue(WzStrUtil.defaultString(lib.getServicesProvided()));
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出服务机构列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    public static void writeEnterpriseExcel(List<LibEnterprise> list, String strJson, OutputStream out) {
        JSONObject jsonStr = JSON.parseObject(strJson);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("企业列表");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        Row row = sheet.createRow(0);
        Cell c = null;

        HSSFCellStyle style = getStyle(wb);

        int count = 0;
        String[] columns = {"序号","编号","企业名称","统一社会信用代码","企业规模（注册资金）","注册时间",
                "注册类型","所在地区","通讯地址","邮编","是否高新技术企业","企业所在地性质",
                "企业所在地性质（其他）","所在国家高新区","所在国家高新区（其他）","是否上市","股票代码",
                "上市地点","上市地点（其他）","所属领域","所属领域（其他）","主营产品",
                "法人姓名","法人手机","法人邮箱","联系人姓名","联系人职务","联系人手机","联系人邮箱",
                "发明专利数量（件）","国家新药数量(件)","实用新型数量(件)","集成电路布图设计专有权数量(件)",
                "外观设计数量(件)","国防专利数量(件)","软件著作权数量(件)","其他数量(件)",
                "研发团队总人数(人)","本科及以上人数(人)","中高级职称以上人数(人)","研发团队高层次人才数(人)",
                "高层次人才类型","高层次人才类型（其他）","去年研发投入（万元）","前年研发投入（万元）",
                "大前年研发投入（万元）","平台建设情况","平台建设情况（其他）","是否承担过武器装备科研生产任务",
                "三证已获得情况","保密资格一级获得时间","保密资格二级获得时间","保密资格三级获得时间",
                "科研生产许可证获得时间","承制单位资格获得时间","是否计划申请","军民融合建设管理情况",
                "军民融合建设管理情况（专人姓名）","军民融合建设管理情况（专人职务）","军民融合建设管理情况（专人电话）",
                "可用于军民融合共享的资源情况","民参军的障碍及困难","民参军的障碍及困难（其他）",
                "民参军自身存在的问题","对军民科技协同创新的相关建议","有参军潜力的技术成果", "协同创新项目"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }

        count = 1;
        for(LibEnterprise lib : list){
            row = sheet.createRow(count++);
            c = row.createCell(0);
            c.setCellValue(count - 1);
            c = row.createCell(1);
            c.setCellValue(WzStrUtil.defaultString(lib.getNumber()));
            c = row.createCell(2);
            c.setCellValue(WzStrUtil.defaultString(lib.getName()));
            c = row.createCell(3);
            c.setCellValue(WzStrUtil.defaultString(lib.getCode()));
            c = row.createCell(4);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "企业规模", lib.getRegisteredCapital())));
            c = row.createCell(5);
            c.setCellValue(WzStrUtil.defaultString(lib.getRegisteredTime()));
            c = row.createCell(6);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "注册类型", lib.getRegisteredType())));
            c = row.createCell(7);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.getPcaa(lib.getCountry())));
            c = row.createCell(8);
            c.setCellValue(WzStrUtil.defaultString(lib.getAddress()));
            c = row.createCell(9);
            c.setCellValue(WzStrUtil.defaultString(lib.getZipCode()));
            c = row.createCell(10);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "是否高新", lib.getIsHighNewTech())));
            c = row.createCell(11);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "企业所在地性质", lib.getLocationNature())));
            c = row.createCell(12);
            c.setCellValue(WzStrUtil.defaultString(lib.getLocationNatureOther()));
            c = row.createCell(13);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所在国家高新区", lib.getHighTechZone())));
            c = row.createCell(14);
            c.setCellValue(WzStrUtil.defaultString(lib.getHighTechZoneOther()));//所在国家高新区其他
            c = row.createCell(15);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "是否上市", lib.getIsListed())));
            c = row.createCell(16);
            c.setCellValue(WzStrUtil.defaultString(lib.getStockCode()));
            c = row.createCell(17);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "上市地点", lib.getListedPlace())));
            c = row.createCell(18);
            c.setCellValue(WzStrUtil.defaultString(lib.getListedPlaceOther()));
            c = row.createCell(19);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所属领域", lib.getDomain())));
            c = row.createCell(20);
            c.setCellValue(WzStrUtil.defaultString(lib.getDomainOther()));
            c = row.createCell(21);
            c.setCellValue(WzStrUtil.defaultString(lib.getProduct()));//主营产品
            c = row.createCell(22);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpname()));
            c = row.createCell(23);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpphone()));
            c = row.createCell(24);
            c.setCellValue(WzStrUtil.defaultString(lib.getLpemail()));
            c = row.createCell(25);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxname()));
            c = row.createCell(26);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxzw()));
            c = row.createCell(27);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxphone()));
            c = row.createCell(28);
            c.setCellValue(WzStrUtil.defaultString(lib.getLxemail()));

            c = row.createCell(29);
            c.setCellValue(WzStrUtil.defaultString(lib.getInventionNum()));
            c = row.createCell(30);
            c.setCellValue(WzStrUtil.defaultString(lib.getNewDrugNum()));
            c = row.createCell(31);
            c.setCellValue(WzStrUtil.defaultString(lib.getUtilityModelNum()));
            c = row.createCell(32);
            c.setCellValue(WzStrUtil.defaultString(lib.getIcNum()));
            c = row.createCell(33);
            c.setCellValue(WzStrUtil.defaultString(lib.getDesignNum()));
            c = row.createCell(34);
            c.setCellValue(WzStrUtil.defaultString(lib.getNationalNum()));
            c = row.createCell(35);
            c.setCellValue(WzStrUtil.defaultString(lib.getSoftwareNum()));
            c = row.createCell(36);
            c.setCellValue(WzStrUtil.defaultString(lib.getOtherInventionNum()));

            c = row.createCell(37);
            c.setCellValue(WzStrUtil.defaultString(lib.getDeveloperNumber()));
            c = row.createCell(38);
            c.setCellValue(WzStrUtil.defaultString(lib.getUndergraduateNum()));
            c = row.createCell(39);
            c.setCellValue(WzStrUtil.defaultString(lib.getSeniorNum()));
            c = row.createCell(40);
            c.setCellValue(WzStrUtil.defaultString(lib.getHighTitleNum()));
            c = row.createCell(41);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "高层次人才类型", lib.getHighTitleType())));
            c = row.createCell(42);
            c.setCellValue(WzStrUtil.defaultString(lib.getHighTitleTypeOther()));

            c = row.createCell(43);
            c.setCellValue(WzStrUtil.defaultString(lib.getRdAmountLast()));
            c = row.createCell(44);
            c.setCellValue(WzStrUtil.defaultString(lib.getRdAmountBefore()));
            c = row.createCell(45);
            c.setCellValue(WzStrUtil.defaultString(lib.getRdAmountPrevious()));

            c = row.createCell(46);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "平台建设情况", lib.getPlatform())));
            c = row.createCell(47);
            c.setCellValue(WzStrUtil.defaultString(lib.getPlatformOther()));

            c = row.createCell(48);
            c.setCellValue(WzStrUtil.defaultString("1".equals(lib.getIsResearchWeapon()) ? "是" : "否"));
            c = row.createCell(49);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "三证已获得", lib.getWeaponThreeEvidence())));
            c = row.createCell(50);
            c.setCellValue(WzStrUtil.defaultString(lib.getSecrecyOneTime()));
            c = row.createCell(51);
            c.setCellValue(WzStrUtil.defaultString(lib.getSecrecyTwoTime()));
            c = row.createCell(52);
            c.setCellValue(WzStrUtil.defaultString(lib.getSecrecyThreeTime()));
            c = row.createCell(53);
            c.setCellValue(WzStrUtil.defaultString(lib.getProduceTime()));
            c = row.createCell(54);
            c.setCellValue(WzStrUtil.defaultString(lib.getSupplierTime()));
            c = row.createCell(55);
            c.setCellValue(WzStrUtil.defaultString("1".equals(lib.getPlanApply()) ? "是" : "否"));

            c = row.createCell(56);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "军民融合建设管理情况", lib.getManagement())));
            c = row.createCell(57);
            c.setCellValue(WzStrUtil.defaultString(lib.getManagementName()));
            c = row.createCell(58);
            c.setCellValue(WzStrUtil.defaultString(lib.getManagementZw()));
            c = row.createCell(59);
            c.setCellValue(WzStrUtil.defaultString(lib.getManagementPhone()));

            c = row.createCell(60);
            c.setCellValue(WzStrUtil.defaultString(lib.getSharedResources()));
            c = row.createCell(61);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "民参军过程中遇到的障碍及困难", lib.getJmrhDifficulty())));
            c = row.createCell(62);
            c.setCellValue(WzStrUtil.defaultString(lib.getJmrhDifficultyOther()));

            c = row.createCell(63);
            c.setCellValue(WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "民参军过程中企业自身存在的问题", lib.getJmrhProblem())));

            c = row.createCell(64);
            c.setCellValue(WzStrUtil.defaultString(lib.getJmrhSuggest()));
            c = row.createCell(65);
            c.setCellValue(WzStrUtil.defaultString(lib.getJmrhTech()));

            // 承担的省部级以上军民科技协同创新项目
            c = row.createCell(66);
            String prjStr = lib.getProject();
            if (WzStrUtil.isNotBlank(lib.getProject())) {
                try {
                    JSONArray prjJsonArr = JSON.parseArray(lib.getProject());
                    JSONObject prjJsonRow = null;
                    StringBuilder tmpRowStr = new StringBuilder("");
                    for (int i = 0; i < prjJsonArr.size(); i++) {
                        prjJsonRow = prjJsonArr.getJSONObject(i);
                        tmpRowStr.append("项目名:" + WzStrUtil.defaultString(prjJsonRow.getString("name"))).append("|");
                        JSONArray tmpDateArr = prjJsonRow.getJSONArray("time");
                        if (null == tmpDateArr || 0 >= tmpDateArr.size()) {
                            tmpRowStr.append("时间起:未填写|");
                            tmpRowStr.append("时间止:未填写|");
                        } else if (1 == tmpDateArr.size()) {
                            tmpRowStr.append("时间起:" + WzStrUtil.defaultString((String) tmpDateArr.get(0))).append("|");
                            tmpRowStr.append("时间止:未填写|");
                        } else {
                            tmpRowStr.append("时间起:" + WzStrUtil.defaultString((String) tmpDateArr.get(0))).append("|");
                            tmpRowStr.append("时间止:" + WzStrUtil.defaultString((String) tmpDateArr.get(1))).append("|");
                        }
                        tmpRowStr.append("项目来源:" + WzStrUtil.defaultString(prjJsonRow.getString("source"))).append("|");
                        tmpRowStr.append("服务部门:" + WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "服务部门", prjJsonRow.getString("fwbm")))).append("|");
                        tmpRowStr.append("服务部门（其他）:" + WzStrUtil.defaultString(prjJsonRow.getString("fwbmOther"))).append("|");
                        tmpRowStr.append("执行情况:" + WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "执行情况", prjJsonRow.getString("zxqk")))).append("|");
                        tmpRowStr.append("\r\n");
                    }
                    prjStr = tmpRowStr.toString();
                } catch (Exception e) {
                    logger.error("解析承担的省部级以上军民科技协同创新项目情况异常", e);
                }
            }
            c.setCellValue(prjStr);
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出企业列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }

    void setTags(Map<String, Integer> map, HSSFSheet sheet, Row row, Cell c, HSSFCellStyle style){
        /**  
         * 合并单元格  
         *    第一个参数：第一个单元格的行数（从0开始）  
         *    第二个参数：第二个单元格的行数（从0开始）  
         *    第三个参数：第一个单元格的列数（从0开始）  
         *    第四个参数：第二个单元格的列数（从0开始）  
         */ 
        CellRangeAddress range = null;
        
        int count = 0;
        for(Entry<String, Integer> entry : map.entrySet()) {
            range = new CellRangeAddress(0, 0, count, entry.getValue());
            sheet.addMergedRegion(range);
            c = row.createCell(count);
            c.setCellValue(entry.getKey());
            c.setCellStyle(style);
            sheet.setColumnWidth(count, entry.getKey().length() * 512);
            count = entry.getValue() + 1;
        }
    }

    public static boolean isInteger(String str) {  
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");  
        return pattern.matcher(str).matches();  
  }
    
    public JSONArray excelTranJson(String path, int userType, String strJson) {
        JSONObject jsonStr = null;
        if (WzStrUtil.isNotBlank(strJson)) {
            jsonStr = JSON.parseObject(strJson);
        }
        InputStream is = null;
        Workbook wb = null;
//        XSSFWorkbook wb = null;
        Sheet sheet = null;
        JSONArray jsonArray = null;
        JSONObject json = null;
        boolean isLoadFile = true;
        try {
            is = new FileInputStream(path);
            wb = new XSSFWorkbook(is);
        } catch (Exception ex) {
            logger.error("通过XSSFWorkbook导入是发生异常", ex);
            try {
                wb = new HSSFWorkbook(is);
            } catch (Exception exx) {
                logger.error("通过HSSFWorkbook导入是发生异常", exx);
                isLoadFile = false;
            }
        }
        try {
            sheet = wb.getSheetAt(0);
            jsonArray = new JSONArray();
            if (isLoadFile) {
                switch(userType) {
                    case 1:
                        jsonArray = this.toExpertForm(sheet, jsonStr);
                        break;
                    case 2:
                        jsonArray = this.toEnterpriseForm(sheet, jsonStr);
                        break;
                    case 3:
                        jsonArray = this.toServiceForm(sheet, jsonStr);
                        break;
                    case 4:
                        jsonArray = this.toCollegeForm(sheet, jsonStr);
                        break;
                    case 6:
                        jsonArray = this.toResultForm(sheet, jsonStr);
                        break;
                    case 7:
                        jsonArray = this.toReqForm(sheet, jsonStr);
                        break;
                    default:
                }
            }
        } catch (Exception ex) {
            logger.error("Excel数据处理中发生异常", ex);
            throw ex;
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                logger.error("关闭Excel文档异常", e);
            }
            try {
                is.close();
            } catch (IOException e) {
                logger.error("关闭Excel输入流异常", e);
            }
        }
        if(jsonArray == null) {
            return null;
        }
        return jsonArray;
    }

    private JSONArray toReqForm(Sheet sheet, JSONObject jsonStr){
        JSONArray forms = new JSONArray();

        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        String cellValue = null;
        JSONObject formVo = null;
        JSONObject formShowVo = null;
        int idx = 2;
        for (int i = 1; i <= lastRowNum; i++) {
            formVo = new JSONObject();
            formShowVo = new JSONObject();
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            idx = 2;
            cell = row.getCell(idx++);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            }
            formVo.put("re_name", getCellValue(cell));
            formShowVo.put("re_name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("name", getCellValue(cell));
            formShowVo.put("name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("code", getCellValue(cell));
            formShowVo.put("code", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("address", getCellValue(cell));
            formShowVo.put("address", getCellValue(cell));
            cell = row.getCell(idx++);
            String p = getCellValue(cell);
            cell = row.getCell(idx++);
            String c = getCellValue(cell);
            cell = row.getCell(idx++);
            String z = getCellValue(cell);
            JSONArray pcz = new JSONArray();
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal(null, null, p));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("p", (String) pcz.get(0), c));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("c", (String) pcz.get(1), z));
            formVo.put("country", pcz);
            formShowVo.put("country", p + "," + c + "," + z);

            cell = row.getCell(idx++);
            formVo.put("fzname", getCellValue(cell));
            formShowVo.put("fzname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzzw", getCellValue(cell));
            formShowVo.put("fzzw", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzphone", getCellValue(cell));
            formShowVo.put("fzphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzemail", getCellValue(cell));
            formShowVo.put("fzemail", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzname2", getCellValue(cell));
            formShowVo.put("fzname2", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzzw2", getCellValue(cell));
            formShowVo.put("fzzw2", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzphone2", getCellValue(cell));
            formShowVo.put("fzphone2", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzemail2", getCellValue(cell));
            formShowVo.put("fzemail2", getCellValue(cell));

            cell = row.getCell(idx++);
            JSONArray dm = getJsonArrayByStr(getCellValue(cell));
            formVo.put("domain", dm);
            formShowVo.put("domain", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所属领域", JSONArray.toJSONString(dm))));
            cell = row.getCell(idx++);
            formVo.put("domainOther", getCellValue(cell));
            formShowVo.put("domainOther", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("reqDesc", getCellValue(cell));
            formShowVo.put("reqDesc", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("reqNew", getCellValue(cell));
            formShowVo.put("reqNew", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("reqOther", getCellValue(cell));
            formShowVo.put("reqOther", getCellValue(cell));

            cell = row.getCell(idx++);
            JSONArray rb = getJsonArrayByStr(getCellValue(cell));
            formVo.put("reqBack", rb);
            formShowVo.put("reqBack", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "需求背景", JSONArray.toJSONString(rb))));
            cell = row.getCell(idx++);
            formVo.put("reqBackOther", getCellValue(cell));
            formShowVo.put("reqBackOther", getCellValue(cell));

            cell = row.getCell(idx++);
            JSONArray rg = getJsonArrayByStr(getCellValue(cell));
            formVo.put("reqGet", rg);
            formShowVo.put("reqGet", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "合作方式", JSONArray.toJSONString(rg))));

            formVo.put("picLogo", "def/xqlogo.png.png");
            formShowVo.put("picLogo", "def/xqlogo.png.png");
            formVo.put("picOrgLicense", "def/companyZZ.png");
            formShowVo.put("picOrgLicense", "def/companyZZ.png");
            formVo.put("picLpLicense", "def/companyZS.png");
            formShowVo.put("picLpLicense", "def/companyZS.png");
            formVo.put("picLpIdCardFront", "def/idfront.png");
            formShowVo.put("picLpIdCardFront", "def/idfront.png");
            formVo.put("picLpIdCardBack", "def/idback.png");
            formShowVo.put("picLpIdCardBack", "def/idback.png");
            formVo.put("picLpIdCardInHand", "def/handPhoto.jpg");
            formShowVo.put("picLpIdCardInHand", "def/handPhoto.jpg");
            formVo.put("picCommitmentLetter", "def/commitment.png");
            formShowVo.put("picCommitmentLetter", "def/commitment.png");

            formVo.put("search_param", JSON.toJSONString(formShowVo));
            forms.add(formVo);
        }
        return forms;
    }

    private JSONArray toResultForm(Sheet sheet, JSONObject jsonStr){
        JSONArray forms = new JSONArray();

        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        String cellValue = null;
        JSONObject formVo = null;
        JSONObject formShowVo = null;
        int idx = 2;
        for (int i = 1; i <= lastRowNum; i++) {
            formVo = new JSONObject();
            formShowVo = new JSONObject();
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            idx = 2;
            cell = row.getCell(idx++);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            }
            formVo.put("re_name", getCellValue(cell));
            formShowVo.put("re_name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("name", getCellValue(cell));
            formShowVo.put("name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("code", getCellValue(cell));
            formShowVo.put("code", getCellValue(cell));
            cell = row.getCell(idx++);
            String p = getCellValue(cell);
            cell = row.getCell(idx++);
            String c = getCellValue(cell);
            cell = row.getCell(idx++);
            String z = getCellValue(cell);
            JSONArray pcz = new JSONArray();
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal(null, null, p));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("p", (String) pcz.get(0), c));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("c", (String) pcz.get(1), z));
            formVo.put("country", pcz);
            formShowVo.put("country", p + "," + c + "," + z);
            cell = row.getCell(idx++);
            formVo.put("address", getCellValue(cell));
            formShowVo.put("address", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzname", getCellValue(cell));
            formShowVo.put("fzname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzphone", getCellValue(cell));
            formShowVo.put("fzphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzemail", getCellValue(cell));
            formShowVo.put("fzemail", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzname2", getCellValue(cell));
            formShowVo.put("fzname2", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzphone2", getCellValue(cell));
            formShowVo.put("fzphone2", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fzemail2", getCellValue(cell));
            formShowVo.put("fzemail2", getCellValue(cell));

            cell = row.getCell(idx++);
            JSONArray dm = getJsonArrayByStr(getCellValue(cell));
            formVo.put("domain", dm);
            formShowVo.put("domain", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所属领域", JSONArray.toJSONString(dm))));
            cell = row.getCell(idx++);
            formVo.put("domainOther", getCellValue(cell));
            formShowVo.put("domainOther", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("resDesc", getCellValue(cell));
            formShowVo.put("resDesc", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("applyLY", getCellValue(cell));
            formShowVo.put("applyLY", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("newIdea", getCellValue(cell));
            formShowVo.put("newIdea", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("sqPatent", getCellValue(cell));
            formShowVo.put("sqPatent", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("havePatent", getCellValue(cell));
            formShowVo.put("havePatent", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("otherPatent", getCellValue(cell));
            formShowVo.put("otherPatent", getCellValue(cell));

            cell = row.getCell(idx++);
            String prr = getCellValue(cell);
            if (WzStrUtil.isBlank(prr)) {
                formVo.put("patents", new JSONArray());
                formShowVo.put("patents", new JSONArray());
            } else {
                prr = prr.replace("\r", "");
                prr = prr.replace("\n", "");
                String[] prrRowStrArr = prr.split("\\|", -1);
                JSONArray prrRows = new JSONArray();
                JSONArray prrShowRows = new JSONArray();
                String[] prrCols = null;
                JSONObject prrRow = null;
                JSONObject prrShowRow = null;
                for (String prrRowStr : prrRowStrArr) {
                    if (WzStrUtil.isBlank(prrRowStr)) {
                        continue;
                    }
                    prrCols = prrRowStr.split("\\\\", -1);
                    prrRow = new JSONObject();
                    prrShowRow = new JSONObject();
                    if (4 < prrCols.length) {
                        prrRow.put("projectname", prrCols[0]);
                        prrShowRow.put("projectname", prrCols[0]);
                        prrRow.put("projecType", prrCols[1]);
                        prrShowRow.put("projecType", prrCols[1]);
                        prrRow.put("projectNum", prrCols[2]);
                        prrShowRow.put("projectNum", prrCols[2]);
                        prrRow.put("projectPer", prrCols[3]);
                        prrShowRow.put("projectPer", prrCols[3]);
                        prrRow.put("projectTime", prrCols[4]);
                        prrShowRow.put("projectTime", prrCols[4]);
                        prrRows.add(prrRow);
                        prrShowRows.add(prrShowRow);
                    }
                }
                formVo.put("patents", prrRows);
                formShowVo.put("patents", prrShowRows);
            }

            cell = row.getCell(idx++);
            JSONArray stg = getJsonArrayByStr(getCellValue(cell));
            formVo.put("stage", stg);
            formShowVo.put("stage", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所处阶段", JSONArray.toJSONString(stg))));

            cell = row.getCell(idx++);
            JSONArray cpt = getJsonArrayByStr(getCellValue(cell));
            formVo.put("cooperation", cpt);
            formShowVo.put("cooperation", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "合作方式", JSONArray.toJSONString(cpt))));

            formVo.put("picLogo", "def/cglogo.png.png");
            formShowVo.put("picLogo", "def/cglogo.png.png");
            formVo.put("picOrgLicense", "def/companyZZ.png");
            formShowVo.put("picOrgLicense", "def/companyZZ.png");
            formVo.put("picLpLicense", "def/companyZS.png");
            formShowVo.put("picLpLicense", "def/companyZS.png");
            formVo.put("picLpIdCardFront", "def/idfront.png");
            formShowVo.put("picLpIdCardFront", "def/idfront.png");
            formVo.put("picLpIdCardBack", "def/idback.png");
            formShowVo.put("picLpIdCardBack", "def/idback.png");
            formVo.put("picLpIdCardInHand", "def/handPhoto.jpg");
            formShowVo.put("picLpIdCardInHand", "def/handPhoto.jpg");
            formVo.put("picCommitmentLetter", "def/commitment.png");
            formShowVo.put("picCommitmentLetter", "def/commitment.png");

            formVo.put("search_param", JSON.toJSONString(formShowVo));
            forms.add(formVo);
        }
        return forms;
    }

    private JSONArray toCollegeForm(Sheet sheet, JSONObject jsonStr){
        JSONArray forms = new JSONArray();

        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        String cellValue = null;
        JSONObject formVo = null;
        JSONObject formShowVo = null;
        int idx = 2;
        for (int i = 1; i <= lastRowNum; i++) {
            formVo = new JSONObject();
            formShowVo = new JSONObject();
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            idx = 2;
            cell = row.getCell(idx++);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            }
            formVo.put("name", getCellValue(cell));
            formShowVo.put("name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("code", getCellValue(cell));
            formShowVo.put("code", getCellValue(cell));
            cell = row.getCell(idx++);
            String p = getCellValue(cell);
            cell = row.getCell(idx++);
            String c = getCellValue(cell);
            cell = row.getCell(idx++);
            String z = getCellValue(cell);
            JSONArray pcz = new JSONArray();
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal(null, null, p));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("p", (String) pcz.get(0), c));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("c", (String) pcz.get(1), z));
            formVo.put("country", pcz);
            formShowVo.put("country", p + "," + c + "," + z);
            cell = row.getCell(idx++);
            formVo.put("adress", getCellValue(cell));
            formShowVo.put("adress", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("zip_code", getCellValue(cell));
            formShowVo.put("zip_code", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("unit_url", getCellValue(cell));
            formShowVo.put("unit_url", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("introduction", getCellValue(cell));
            formShowVo.put("introduction", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("major_platform", getCellValue(cell));
            formShowVo.put("major_platform", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxname", getCellValue(cell));
            formShowVo.put("lxname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxmobile", getCellValue(cell));
            formShowVo.put("lxmobile", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxemail", getCellValue(cell));
            formShowVo.put("lxemail", getCellValue(cell));

            formVo.put("picLogo", "def/gxlogo.png.png");
            formShowVo.put("picLogo", "def/gxlogo.png.png");
            formVo.put("picOrgLicense", "def/companyZZ.png");
            formShowVo.put("picOrgLicense", "def/companyZZ.png");
            formVo.put("picLpLicense", "def/companyZS.png");
            formShowVo.put("picLpLicense", "def/companyZS.png");
            formVo.put("picLmIdCardFront", "def/idfront.png");
            formShowVo.put("picLmIdCardFront", "def/idfront.png");
            formVo.put("picLmIdCardBack", "def/idback.png");
            formShowVo.put("picLmIdCardBack", "def/idback.png");
            formVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formShowVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formVo.put("search_param", JSON.toJSONString(formShowVo));
            forms.add(formVo);
        }
        return forms;
    }

    private JSONArray toServiceForm(Sheet sheet, JSONObject jsonStr){
        JSONArray forms = new JSONArray();

        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        String cellValue = null;
        JSONObject formVo = null;
        JSONObject formShowVo = null;
        int idx = 2;
        for (int i = 1; i <= lastRowNum; i++) {
            formVo = new JSONObject();
            formShowVo = new JSONObject();
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            idx = 2;
            cell = row.getCell(idx++);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            }
            formVo.put("name", getCellValue(cell));
            formShowVo.put("name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("code", getCellValue(cell));
            formShowVo.put("code", getCellValue(cell));
            cell = row.getCell(idx++);
            String p = getCellValue(cell);
            cell = row.getCell(idx++);
            String c = getCellValue(cell);
            cell = row.getCell(idx++);
            String z = getCellValue(cell);
            JSONArray pcz = new JSONArray();
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal(null, null, p));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("p", (String) pcz.get(0), c));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("c", (String) pcz.get(1), z));
            formVo.put("country", pcz);
            formShowVo.put("country", p + "," + c + "," + z);
            cell = row.getCell(idx++);
            formVo.put("address", getCellValue(cell));
            formShowVo.put("address", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("ecode", getCellValue(cell));
            formShowVo.put("ecode", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray rn = getJsonArrayByStr(getCellValue(cell));
            formVo.put("registerNature", rn);
            formShowVo.put("registerNature", WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "机构性质", JSONArray.toJSONString(rn))));
            cell = row.getCell(idx++);
            formVo.put("mechregisterNatureOther", getCellValue(cell));
            formShowVo.put("mechregisterNatureOther", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray ot = getJsonArrayByStr(getCellValue(cell));
            formVo.put("orgType", ot);
            formShowVo.put("orgType", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "机构类别", JSONArray.toJSONString(ot))));
            cell = row.getCell(idx++);
            formVo.put("orgTypeOther", getCellValue(cell));
            formShowVo.put("orgTypeOther", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("fdname", getCellValue(cell));
            formShowVo.put("fdname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fdtel", getCellValue(cell));
            formShowVo.put("fdtel", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fdphone", getCellValue(cell));
            formShowVo.put("fdphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fdemail", getCellValue(cell));
            formShowVo.put("fdemail", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("linkman", getCellValue(cell));
            formShowVo.put("linkman", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxzw", getCellValue(cell));
            formShowVo.put("lxzw", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxtel", getCellValue(cell));
            formShowVo.put("lxtel", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxphone", getCellValue(cell));
            formShowVo.put("lxphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxemail", getCellValue(cell));
            formShowVo.put("lxemail", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("service_amount_last", getCellValue(cell));
            formShowVo.put("service_amount_last", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_quantity_last", getCellValue(cell));
            formShowVo.put("service_quantity_last", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_amount_before", getCellValue(cell));
            formShowVo.put("service_amount_before", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_quantity_before", getCellValue(cell));
            formShowVo.put("service_quantity_before", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_amount_previous", getCellValue(cell));
            formShowVo.put("service_amount_previous", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_quantity_previous", getCellValue(cell));
            formShowVo.put("service_quantity_previous", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("perNum", getCellValue(cell));
            formShowVo.put("perNum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("underPer", getCellValue(cell));
            formShowVo.put("underPer", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("MHPer", getCellValue(cell));
            formShowVo.put("MHPer", getCellValue(cell));

            cell = row.getCell(idx++);
            String rr = getCellValue(cell);
            if (WzStrUtil.isBlank(rr)) {
                formVo.put("PorcolumnDefinitions", new JSONArray());
                formShowVo.put("PorcolumnDefinitions", new JSONArray());
            } else {
                rr = rr.replace("\r", "");
                rr = rr.replace("\n", "");
                String[] rrRowStrArr = rr.split("\\|", -1);
                JSONArray rrRows = new JSONArray();
                JSONArray rrShowRows = new JSONArray();
                String[] rrCols = null;
                JSONObject rrRow = null;
                JSONObject rrShowRow = null;
                for (String rrRowStr : rrRowStrArr) {
                    if (WzStrUtil.isBlank(rrRowStr)) {
                        continue;
                    }
                    rrCols = rrRowStr.split("\\\\", -1);
                    rrRow = new JSONObject();
                    rrShowRow = new JSONObject();
                    if (6 < rrCols.length) {
                        rrRow.put("name", rrCols[0]);
                        rrShowRow.put("name", rrCols[0]);
                        JSONArray timeArr = new JSONArray();
                        timeArr.add(rrCols[1]);
                        timeArr.add(rrCols[2]);
                        rrRow.put("time", timeArr);
                        rrShowRow.put("time", timeArr);
                        rrRow.put("source", rrCols[3]);
                        rrShowRow.put("source", rrCols[3]);
                        JSONArray rs = getJsonArrayByStr(rrCols[4]);
                        rrRow.put("registerSupport", rs);
                        rrShowRow.put("registerSupport", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "资助方式", JSONArray.toJSONString(rs))));
                        rrRow.put("mechregisterSupportOther", rrCols[5]);
                        rrShowRow.put("mechregisterSupportOther", rrCols[5]);
                        JSONArray zqk = getJsonArrayByStr(rrCols[6]);
                        rrRow.put("registerImplement", zqk);
                        rrShowRow.put("registerImplement", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "执行情况", JSONArray.toJSONString(zqk))));
                        rrRows.add(rrRow);
                        rrShowRows.add(rrShowRow);
                    }
                }
                formVo.put("PorcolumnDefinitions", rrRows);
                formShowVo.put("PorcolumnDefinitions", rrShowRows);
            }

            cell = row.getCell(idx++);
            String hrr = getCellValue(cell);
            if (WzStrUtil.isBlank(hrr)) {
                formVo.put("honor", new JSONArray());
                formShowVo.put("honor", new JSONArray());
            } else {
                rr = rr.replace("\r", "");
                rr = rr.replace("\n", "");
                String[] hrrRowStrArr = hrr.split("\\|", -1);
                JSONArray hrrRows = new JSONArray();
                JSONArray hrrShowRows = new JSONArray();
                String[] hrrCols = null;
                JSONObject hrrRow = null;
                JSONObject hrrShowRow = null;
                for (String hrrRowStr : hrrRowStrArr) {
                    if (WzStrUtil.isBlank(hrrRowStr)) {
                        continue;
                    }
                    hrrCols = hrrRowStr.split("\\\\", -1);
                    hrrRow = new JSONObject();
                    hrrShowRow = new JSONObject();
                    if (2 < hrrCols.length) {
                        hrrRow.put("name", hrrCols[0]);
                        hrrShowRow.put("name", hrrCols[0]);
                        hrrRow.put("time", hrrCols[1]);
                        hrrShowRow.put("time", hrrCols[1]);
                        hrrRow.put("issuingAgency", hrrCols[2]);
                        hrrShowRow.put("issuingAgency", hrrCols[2]);
                        hrrRows.add(hrrRow);
                        hrrShowRows.add(hrrShowRow);
                    }
                }
                formVo.put("honor", hrrRows);
                formShowVo.put("honor", hrrShowRows);
            }

            cell = row.getCell(idx++);
            String wrhrr = getCellValue(cell);
            if (WzStrUtil.isBlank(wrhrr)) {
                formVo.put("workrPorcolumnDefinitions", new JSONArray());
                formShowVo.put("workrPorcolumnDefinitions", new JSONArray());
            } else {
                rr = rr.replace("\r", "");
                rr = rr.replace("\n", "");
                String[] wrhrrRowStrArr = wrhrr.split("\\|", -1);
                JSONArray wrhrrRows = new JSONArray();
                JSONArray wrhrrShowRows = new JSONArray();
                String[] wrhrrCols = null;
                JSONObject wrhrrRow = null;
                JSONObject wrhrrShowRow = null;
                for (String wrhrrRowStr : wrhrrRowStrArr) {
                    if (WzStrUtil.isBlank(wrhrrRowStr)) {
                        continue;
                    }
                    wrhrrCols = wrhrrRowStr.split("\\\\", -1);
                    wrhrrRow = new JSONObject();
                    wrhrrShowRow = new JSONObject();
                    if (2 < wrhrrCols.length) {
                        wrhrrRow.put("name", wrhrrCols[0]);
                        wrhrrShowRow.put("name", wrhrrCols[0]);
                        wrhrrRow.put("object", wrhrrCols[1]);
                        wrhrrShowRow.put("object", wrhrrCols[1]);
                        JSONArray timeArr = new JSONArray();
                        if (WzStrUtil.isBlank(wrhrrCols[2])) {
                            wrhrrRow.put("time", timeArr);
                            wrhrrShowRow.put("time", timeArr);
                        } else {
                            String[] times = wrhrrCols[2].split("~");
                            for (String ts : times) {
                                timeArr.add(ts);
                            }
                            wrhrrRow.put("time", timeArr);
                            wrhrrShowRow.put("time", timeArr);
                        }
                        wrhrrRows.add(wrhrrRow);
                        wrhrrShowRows.add(wrhrrShowRow);
                    }
                }
                formVo.put("workrPorcolumnDefinitions", wrhrrRows);
                formShowVo.put("workrPorcolumnDefinitions", wrhrrShowRows);
            }

            cell = row.getCell(idx++);
            formVo.put("serviceAbout", getCellValue(cell));
            formShowVo.put("serviceAbout", getCellValue(cell));

            formVo.put("picLogo", "def/fwlogo.png.png");
            formShowVo.put("picLogo", "def/fwlogo.png.png");
            formVo.put("picOrgLicense", "def/companyZZ.png");
            formShowVo.put("picOrgLicense", "def/companyZZ.png");
            formVo.put("picLpLicense", "def/companyZS.png");
            formShowVo.put("picLpLicense", "def/companyZS.png");
            formVo.put("picLmIdCardFront", "def/idfront.png");
            formShowVo.put("picLmIdCardFront", "def/idfront.png");
            formVo.put("picLmIdCardBack", "def/idback.png");
            formShowVo.put("picLmIdCardBack", "def/idback.png");
            formVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formShowVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formVo.put("search_param", JSON.toJSONString(formShowVo));
            forms.add(formVo);
        }
        return forms;
    }

    private JSONArray toEnterpriseForm(Sheet sheet, JSONObject jsonStr){
        JSONArray forms = new JSONArray();

        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        String cellValue = null;
        JSONObject formVo = null;
        JSONObject formShowVo = null;
        int idx = 2;
        for (int i = 1; i <= lastRowNum; i++) {
            formVo = new JSONObject();
            formShowVo = new JSONObject();
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            idx = 2;
            cell = row.getCell(idx++);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            }
            formVo.put("name", getCellValue(cell));
            formShowVo.put("name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("code", getCellValue(cell));
            formShowVo.put("code", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray rc = getJsonArrayByStr(getCellValue(cell));
            formVo.put("registered_capital", rc);
            formShowVo.put("registered_capital", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "企业规模", JSONArray.toJSONString(rc))));
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("registerDate", "");
                formShowVo.put("registerDate", "");
            } else {
                formVo.put("registerDate", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("registerDate", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }

            cell = row.getCell(idx++);
            JSONArray rt = getJsonArrayByStr(getCellValue(cell));
            formVo.put("registered_type", rt);
            formShowVo.put("registered_type", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "注册类型", JSONArray.toJSONString(rt))));
            cell = row.getCell(idx++);
            String p = getCellValue(cell);
            cell = row.getCell(idx++);
            String c = getCellValue(cell);
            cell = row.getCell(idx++);
            String z = getCellValue(cell);
            JSONArray pcz = new JSONArray();
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal(null, null, p));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("p", (String) pcz.get(0), c));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("c", (String) pcz.get(1), z));
            formVo.put("country", pcz);
            formShowVo.put("country", p + "," + c + "," + z);
            cell = row.getCell(idx++);
            formVo.put("registeraddress", getCellValue(cell));
            formShowVo.put("registeraddress", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("registerecode", getCellValue(cell));
            formShowVo.put("registerecode", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray ht = getJsonArrayByStr(getCellValue(cell));
            formVo.put("is_high_new_tech", ht);
            formShowVo.put("is_high_new_tech", WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "是否高新", JSONArray.toJSONString(ht))));
            cell = row.getCell(idx++);
            JSONArray rs = getJsonArrayByStr(getCellValue(cell));
            formVo.put("registerSite", rs);
            formShowVo.put("registerSite", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "企业所在地性质", JSONArray.toJSONString(rs))));
            cell = row.getCell(idx++);
            formVo.put("registerSiteOther", getCellValue(cell));
            formShowVo.put("registerSiteOther", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray rhs = getJsonArrayByStr(getCellValue(cell));
            formVo.put("registerHSite", rhs);
            formShowVo.put("registerHSite", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所在国家高新区", JSONArray.toJSONString(rhs))));
            cell = row.getCell(idx++);
            formVo.put("registerHSiteOther", getCellValue(cell));
            formShowVo.put("registerHSiteOther", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("registerMarket", getCellValue(cell));
            formShowVo.put("registerMarket", WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "是否上市", getCellValue(cell))));
            cell = row.getCell(idx++);
            formVo.put("stockCode", getCellValue(cell));
            formShowVo.put("stockCode", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray rms = getJsonArrayByStr(getCellValue(cell));
            formVo.put("registerMarkeSite", rms);
            formShowVo.put("registerMarkeSite", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "上市地点", JSONArray.toJSONString(rms))));
            cell = row.getCell(idx++);
            formVo.put("registerMarkeSiteOther", getCellValue(cell));
            formShowVo.put("registerMarkeSiteOther", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray dm = getJsonArrayByStr(getCellValue(cell));
            formVo.put("domain", dm);
            formShowVo.put("domain", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "所属领域", JSONArray.toJSONString(dm))));
            cell = row.getCell(idx++);
            formVo.put("domainOther", getCellValue(cell));
            formShowVo.put("domainOther", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("product", getCellValue(cell));
            formShowVo.put("product", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lpname", getCellValue(cell));
            formShowVo.put("lpname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lpphone", getCellValue(cell));
            formShowVo.put("lpphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lpemail", getCellValue(cell));
            formShowVo.put("lpemail", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxname", getCellValue(cell));
            formShowVo.put("lxname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxzw", getCellValue(cell));
            formShowVo.put("lxzw", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxphone", getCellValue(cell));
            formShowVo.put("lxphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("lxemail", getCellValue(cell));
            formShowVo.put("lxemail", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("inventionNum", getCellValue(cell));
            formShowVo.put("inventionNum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("newDrugnum", getCellValue(cell));
            formShowVo.put("newDrugnum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("utilityModel", getCellValue(cell));
            formShowVo.put("utilityModel", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("integratedCircuitnum", getCellValue(cell));
            formShowVo.put("integratedCircuitnum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("designnum", getCellValue(cell));
            formShowVo.put("designnum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("nationalNum", getCellValue(cell));
            formShowVo.put("nationalNum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("softwareNum", getCellValue(cell));
            formShowVo.put("softwareNum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("othernum", getCellValue(cell));
            formShowVo.put("othernum", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("researchTeamnum", getCellValue(cell));
            formShowVo.put("researchTeamnum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("bachelorAbovenum", getCellValue(cell));
            formShowVo.put("bachelorAbovenum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("middleLevelnum", getCellValue(cell));
            formShowVo.put("middleLevelnum", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("highleveltalentsnum", getCellValue(cell));
            formShowVo.put("highleveltalentsnum", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray hltt = getJsonArrayByStr(getCellValue(cell));
            formVo.put("highleveltalentType", hltt);
            formShowVo.put("highleveltalentType", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "高层次人才类型", JSONArray.toJSONString(hltt))));
            cell = row.getCell(idx++);
            formVo.put("comhighleveltalentTypeOther", getCellValue(cell));
            formShowVo.put("comhighleveltalentTypeOther", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("service_research_last", getCellValue(cell));
            formShowVo.put("service_research_last", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_research_before", getCellValue(cell));
            formShowVo.put("service_research_before", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("service_research_previous", getCellValue(cell));
            formShowVo.put("service_research_previous", getCellValue(cell));

            cell = row.getCell(idx++);
            JSONArray pf = getJsonArrayByStr(getCellValue(cell));
            formVo.put("platform", pf);
            formShowVo.put("platform", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "平台建设情况", JSONArray.toJSONString(pf))));
            cell = row.getCell(idx++);
            formVo.put("platformOther", getCellValue(cell));
            formShowVo.put("platformOther", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("sfkyrw", getCellValue(cell));
            formShowVo.put("sfkyrw", WzStrUtil.defaultString("1".equals(getCellValue(cell)) ? "是" : "否"));
            cell = row.getCell(idx++);
            JSONArray szg = getJsonArrayByStr(getCellValue(cell));
            formVo.put("szget", szg);
            formShowVo.put("szget", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "三证已获得", JSONArray.toJSONString(szg))));
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("qdtime1", "");
                formShowVo.put("qdtime1", "");
            } else {
                formVo.put("qdtime1", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("qdtime1", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("qdtime2", "");
                formShowVo.put("qdtime2", "");
            } else {
                formVo.put("qdtime2", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("qdtime2", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("qdtime3", "");
                formShowVo.put("qdtime3", "");
            } else {
                formVo.put("qdtime3", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("qdtime3", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("qdtime4", "");
                formShowVo.put("qdtime4", "");
            } else {
                formVo.put("qdtime4", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("qdtime4", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("qdtime5", "");
                formShowVo.put("qdtime5", "");
            } else {
                formVo.put("qdtime5", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("qdtime5", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }
            cell = row.getCell(idx++);
            formVo.put("szwhd", getCellValue(cell));
            formShowVo.put("szwhd", WzStrUtil.defaultString("1".equals(getCellValue(cell)) ? "是" : "否"));

            cell = row.getCell(idx++);
            JSONArray jg = getJsonArrayByStr(getCellValue(cell));
            formVo.put("jmrhgn", jg);
            formShowVo.put("jmrhgn", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "军民融合建设管理情况", JSONArray.toJSONString(jg))));
            cell = row.getCell(idx++);
            formVo.put("jmrhgnName", getCellValue(cell));
            formShowVo.put("jmrhgnName", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("jmrhgnZW", getCellValue(cell));
            formShowVo.put("jmrhgnZW", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("jmrhgnTel", getCellValue(cell));
            formShowVo.put("jmrhgnTel", getCellValue(cell));

            cell = row.getCell(idx++);
            formVo.put("zyqk", getCellValue(cell));
            formShowVo.put("zyqk", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray kn = getJsonArrayByStr(getCellValue(cell));
            formVo.put("ydkn", kn);
            formShowVo.put("ydkn", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "民参军过程中遇到的障碍及困难", JSONArray.toJSONString(kn))));
            cell = row.getCell(idx++);
            formVo.put("comydknOther", getCellValue(cell));
            formShowVo.put("comydknOther", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray pbm = getJsonArrayByStr(getCellValue(cell));
            formVo.put("problem", pbm);
            formShowVo.put("problem", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "民参军过程中企业自身存在的问题", JSONArray.toJSONString(pbm))));
            cell = row.getCell(idx++);
            formVo.put("xgjy", getCellValue(cell));
            formShowVo.put("xgjy", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("jscg", getCellValue(cell));
            formShowVo.put("jscg", getCellValue(cell));

            cell = row.getCell(idx++);
            String rr = getCellValue(cell);
            if (WzStrUtil.isBlank(rr)) {
                formVo.put("comPorcolumnDefinitions", new JSONArray());
                formShowVo.put("comPorcolumnDefinitions", new JSONArray());
            } else {
                rr = rr.replace("\r", "");
                rr = rr.replace("\n", "");
                String[] rrRowStrArr = rr.split("\\|", -1);
                JSONArray rrRows = new JSONArray();
                JSONArray rrShowRows = new JSONArray();
                String[] rrCols = null;
                JSONObject rrRow = null;
                JSONObject rrShowRow = null;
                for (String rrRowStr : rrRowStrArr) {
                    if (WzStrUtil.isBlank(rrRowStr)) {
                        continue;
                    }
                    rrCols = rrRowStr.split("\\\\", -1);
                    rrRow = new JSONObject();
                    rrShowRow = new JSONObject();
                    if (6 < rrCols.length) {
                        rrRow.put("name", rrCols[0]);
                        rrShowRow.put("name", rrCols[0]);
                        JSONArray timeArr = new JSONArray();
                        timeArr.add(rrCols[1]);
                        timeArr.add(rrCols[2]);
                        rrRow.put("time", timeArr);
                        rrShowRow.put("time", timeArr);
                        rrRow.put("source", rrCols[3]);
                        rrShowRow.put("source", rrCols[3]);
                        JSONArray fbm = getJsonArrayByStr(rrCols[4]);
                        rrRow.put("fwbm", fbm);
                        rrShowRow.put("fwbm", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "服务部门", JSONArray.toJSONString(fbm))));
                        rrRow.put("fwbmOther", rrCols[5]);
                        rrShowRow.put("fwbmOther", rrCols[5]);
                        JSONArray zqk = getJsonArrayByStr(rrCols[6]);
                        rrRow.put("zxqk", zqk);
                        rrShowRow.put("zxqk", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "执行情况", JSONArray.toJSONString(zqk))));
                        rrRows.add(rrRow);
                        rrShowRows.add(rrShowRow);
                    }
                }
                formVo.put("comPorcolumnDefinitions", rrRows);
                formShowVo.put("comPorcolumnDefinitions", rrShowRows);
            }
            formVo.put("picLogo", "def/qylogo.png");
            formShowVo.put("picLogo", "def/qylogo.png");
            formVo.put("picOrgLicense", "def/companyZZ.png");
            formShowVo.put("picOrgLicense", "def/companyZZ.png");
            formVo.put("picLpLicense", "def/companyZS.png");
            formShowVo.put("picLpLicense", "def/companyZS.png");
            formVo.put("picLmIdCardFront", "def/idfront.png");
            formShowVo.put("picLmIdCardFront", "def/idfront.png");
            formVo.put("picLmIdCardBack", "def/idback.png");
            formShowVo.put("picLmIdCardBack", "def/idback.png");
            formVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formShowVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formVo.put("search_param", JSON.toJSONString(formShowVo));
            forms.add(formVo);
        }
        return forms;
    }

    private JSONArray toExpertForm(Sheet sheet, JSONObject jsonStr){
        JSONArray forms = new JSONArray();
        String [] keys = {"name", "code","sex","bornDate","country0","country1","country2","address","shcool",
                "edu","academic","research_field","researchFieldOther","research_area",
                "work_unit","work_bumen","zwname","zcname","zclevel","mobilephone","telphone","fdemail",
                "success_record","project_desc","research_record",
                "picLogo", "picLmIdCardFront", "picLmIdCardBack", "picLmIdCardInHand", "search_param"};
        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        String cellValue = null;
        JSONObject formVo = null;
        JSONObject formShowVo = null;
        int idx = 2;
        for (int i = 1; i <= lastRowNum; i++) {
            formVo = new JSONObject();
            formShowVo = new JSONObject();
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            idx = 2;
            cell = row.getCell(idx++);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            }
            formVo.put("name", getCellValue(cell));
            formShowVo.put("name", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("code", getCellValue(cell));
            formShowVo.put("code", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("sex", getCellValue(cell));
            formShowVo.put("sex", getCellValue(cell) == null ? "-" : getCellValue(cell).equals("0") ? "女" : "男");
            cell = row.getCell(idx++);
            if (null == cell) {
                formVo.put("bornDate", "");
                formShowVo.put("bornDate", "");
            } else {
                formVo.put("bornDate", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
                formShowVo.put("bornDate", WzDateUtil.dt2Str(cell.getDateCellValue(), WzDateUtil.DT_FMT_6));
            }

            cell = row.getCell(idx++);
            String p = getCellValue(cell);
            cell = row.getCell(idx++);
            String c = getCellValue(cell);
            cell = row.getCell(idx++);
            String z = getCellValue(cell);
            JSONArray pcz = new JSONArray();
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal(null, null, p));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("p", (String) pcz.get(0), c));
            pcz.add(KeyValueTransUtil.getPcaaKeyByVal("c", (String) pcz.get(1), z));
            formVo.put("country", pcz);
            formShowVo.put("country", p + "," + c + "," + z);
            cell = row.getCell(idx++);
            formVo.put("address", getCellValue(cell));
            formShowVo.put("address", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("shcool", getCellValue(cell));
            formShowVo.put("shcool", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("edu", getCellValue(cell));
            formShowVo.put("edu", WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "学历", getCellValue(cell))));
            cell = row.getCell(idx++);
            formVo.put("academic", getCellValue(cell));
            formShowVo.put("academic", WzStrUtil.defaultString(KeyValueTransUtil.transChOnly(jsonStr, "学位", getCellValue(cell))));
            cell = row.getCell(idx++);
            JSONArray rf = getJsonArrayByStr(getCellValue(cell));
            formVo.put("research_field", rf);
            formShowVo.put("research_field", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "研究领域", JSONArray.toJSONString(rf))));
            cell = row.getCell(idx++);
            formVo.put("researchFieldOther", getCellValue(cell));
            formShowVo.put("researchFieldOther", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("research_area", getCellValue(cell));
            formShowVo.put("research_area", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("work_unit", getCellValue(cell));
            formShowVo.put("work_unit", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("work_bumen", getCellValue(cell));
            formShowVo.put("work_bumen", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("zwname", getCellValue(cell));
            formShowVo.put("zwname", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("zcname", getCellValue(cell));
            formShowVo.put("zcname", getCellValue(cell));
            cell = row.getCell(idx++);
            JSONArray zcl = getJsonArrayByStr(getCellValue(cell));
            formVo.put("zclevel", zcl);
            formShowVo.put("zclevel", WzStrUtil.defaultString(KeyValueTransUtil.transCh(jsonStr, "职称级别", JSONArray.toJSONString(zcl))));
            cell = row.getCell(idx++);
            formVo.put("mobilephone", getCellValue(cell));
            formShowVo.put("mobilephone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("telphone", getCellValue(cell));
            formShowVo.put("telphone", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("fdemail", getCellValue(cell));
            formShowVo.put("fdemail", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("success_record", getCellValue(cell));
            formShowVo.put("success_record", getCellValue(cell));
            cell = row.getCell(idx++);
            formVo.put("project_desc", getCellValue(cell));
            formShowVo.put("project_desc", getCellValue(cell));
            cell = row.getCell(idx++);
            String rr = getCellValue(cell);
            if (WzStrUtil.isBlank(rr)) {
                formVo.put("research_record", new JSONArray());
                formShowVo.put("research_record", new JSONArray());
            } else {
                rr = rr.replace("\r", "");
                rr = rr.replace("\n", "");
                String[] rrRowStrArr = rr.split("\\|", -1);
                JSONArray rrRows = new JSONArray();
                String[] rrCols = null;
                JSONObject rrRow = null;
                for (String rrRowStr : rrRowStrArr) {
                    if (WzStrUtil.isBlank(rrRowStr)) {
                        continue;
                    }
                    rrCols = rrRowStr.split("\\\\", -1);
                    rrRow = new JSONObject();
                    if (6 < rrCols.length) {
                        rrRow.put("projectname", rrCols[0]);
                        rrRow.put("projectSrc", rrCols[1]);
                        rrRow.put("finishcon", rrCols[2]);
                        rrRow.put("finishtime", rrCols[3]);
                        rrRow.put("rewname", rrCols[4]);
                        rrRow.put("rewlevel", rrCols[5]);
                        rrRow.put("rewtime", rrCols[6]);
                    }
                    rrRows.add(rrRow);
                }
                formVo.put("research_record", rrRows);
                formShowVo.put("research_record", rrRows);
            }
            formVo.put("picLogo", "def/livePhoto.jpg");
            formShowVo.put("picLogo", "def/livePhoto.jpg");
            formVo.put("picLmIdCardFront", "def/idfront.png");
            formShowVo.put("picLmIdCardFront", "def/idfront.png");
            formVo.put("picLmIdCardBack", "def/idback.png");
            formShowVo.put("picLmIdCardBack", "def/idback.png");
            formVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formShowVo.put("picLmIdCardInHand", "def/handPhoto.jpg");
            formVo.put("search_param", JSON.toJSONString(formShowVo));
            forms.add(formVo);
        }
        return forms;
    }

    private JSONArray getJsonArrayByStr(String s) {
        if (WzStrUtil.isBlank(s)) {
            return new JSONArray();
        }
        JSONArray sJsonArr = new JSONArray();
        String[] sArr = s.split(",");
        if (null == sArr || 0 == sArr.length) {
            return new JSONArray();
        }
        for (String svo : sArr) {
            if (WzStrUtil.isNotBlank(svo)) {
                sJsonArr.add(svo);
            }
        }
        return sJsonArr;
    }

    private String getCellValue(Cell cell) {
        String cellValue = null;
        if (null != cell) {
            switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                cellValue = String.valueOf((int) cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = WzStrUtil.defaultString(cell.getStringCellValue());
                cellValue = WzStrUtil.replaceEachRepeatedly(cellValue, new String[] {" ", "\r", "\n", " "}, new String[] {"", "", "", ""});
                break;
            case FORMULA:
                cellValue = cell.getNumericCellValue() + "";
                // cellValue = String.valueOf(cell.getDateCellValue());
                break;
            case BLANK:
                cellValue = "";
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                break;
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
    
    private static String getQdtime(String qdtime){
        String regEx="[^0-9]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(qdtime);
        return m.replaceAll("").trim();
    }

    public static void writeMajorPlatformExcel(List<CmsSharePlatform> list,OutputStream out) {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("重大平台");
        wb.setActiveSheet(0);
        sheet.createFreezePane(2, 1);

        HSSFCellStyle style = getStyle(wb);
        Row row = sheet.createRow(0);
        Cell c = null;

        int count = 0;
        String[] columns = {"名称","承担单位","领域","级别","链接"};
        for (String label : columns) {
            c = row.createCell(count);
            c.setCellValue(label);
            c.setCellStyle(style);
            sheet.setColumnWidth(count, label.length() * 512);
            count++;
        }
        count = 1;
        int index = 0;
        for (CmsSharePlatform model : list) {
            row = sheet.createRow(count++);
            index = 0;
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(model.getSpName()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(model.getSpCompany()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(model.getSpDomain()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(model.getSpLevel()));
            c = row.createCell(index++);
            c.setCellValue(WzStrUtil.defaultString(model.getSpLink()));
        }
        try {
            wb.write(out);
        } catch (IOException e) {
            logger.error("导出列表文件异常", e);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error("关闭Excel输出异常", e);
        }
        try {
            out.close();
        } catch (IOException e) {
            logger.error("关闭输出流异常", e);
        }
    }


    public List<CmsSharePlatform> excelTranJson(String path) {
        InputStream is = null;
        Workbook wb = null;
        Sheet sheet = null;
        boolean isLoadFile = true;
        try {
            is = new FileInputStream(path);
            wb = new XSSFWorkbook(is);
        } catch (Exception ex) {
            logger.error("通过XSSFWorkbook导入是发生异常", ex);
            try {
                wb = new HSSFWorkbook(is);
            } catch (Exception exx) {
                logger.error("通过HSSFWorkbook导入是发生异常", exx);
                isLoadFile = false;
            }
        }
        try {
            sheet = wb.getSheetAt(0);
            if (isLoadFile) {
                return toMajorPlatform(sheet);
            }
        } catch (Exception ex) {
            logger.error("Excel数据处理中发生异常", ex);
            throw ex;
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                logger.error("关闭Excel文档异常", e);
            }
            try {
                is.close();
            } catch (IOException e) {
                logger.error("关闭Excel输入流异常", e);
            }
        }
        return null;
    }

    private List<CmsSharePlatform> toMajorPlatform(Sheet sheet) {
        List<CmsSharePlatform> list = new ArrayList<>();

        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        for (int i = 1; i <= lastRowNum; i++) {
            row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            CmsSharePlatform model = new CmsSharePlatform();
            // 第一列
            Cell cell = row.getCell(0);
            if (WzStrUtil.isBlank(getCellValue(cell))) {
                continue;
            } else {
                model.setSpName(getCellValue(cell));
            }
            // 第2⃣列
            Cell cell2 = row.getCell(1);
            if (WzStrUtil.isBlank(getCellValue(cell2))) {
                continue;
            } else {
                model.setSpCompany(getCellValue(cell2));
            }
            // 第三列
            Cell cell3 = row.getCell(2);
            if (WzStrUtil.isBlank(getCellValue(cell3))) {
                continue;
            } else {
                model.setSpDomain(getCellValue(cell3));
            }
            // 第四列
            Cell cell4 = row.getCell(3);
            if (WzStrUtil.isBlank(getCellValue(cell4))) {
                continue;
            } else {
                model.setSpLevel(getCellValue(cell4));
            }
            // 第五列
            Cell cell5 = row.getCell(4);
            if (WzStrUtil.isBlank(getCellValue(cell5))) {
                continue;
            } else {
                model.setSpLink(getCellValue(cell5));
            }

            list.add(model);
        }
        return list;
    }
}
